﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text.RegularExpressions;
using MillerApp.CommonLib.Utility;

namespace MillerApp.CommonLib.DataAccess
{
    /// <summary>
    /// 通用数据库访问类，封装了对数据库的常见操作
    /// 作者：周公
    /// 日期：2011-07-18
    /// 博客地址：http://blog.csdn.net/zhoufoxcn 或http://zhoufoxcn.blog.51cto.com
    /// 说明：(1)任何人都可以免费使用，请尽量保持此段说明。
    ///      (2)这个版本还不是最终版本，有任何意见或建议请到http://weibo.com/zhoufoxcn处留言。
    /// </summary>
    public class DBHelper:IDBHelper
    {
        public string ConnectionString { get; protected set; }
        public string ProviderInvariantName { get; protected set; }
        public DbProviderFactory ProviderFactory{get;private set;}

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="providerType">数据库类型枚举，参见<paramref name="providerType"/></param>
        public  DBHelper(string connectionString,string providerInvariantName)
        {
            ConnectionString = connectionString;
            ProviderInvariantName = providerInvariantName;
            ProviderFactory = this.GetDbProviderFactory();
        }
        /// <summary>   
        /// 对数据库执行增删改操作，返回受影响的行数。   
        /// </summary>   
        /// <param name="sql">要执行的增删改的SQL语句</param>   
        /// <param name="parameters">执行增删改语句所需要的参数</param>
        /// <returns></returns>  
        public int ExecuteNonQuery(string sql, IList<IDbDataParameter> parameters)
        {
            return ExecuteNonQuery(sql, parameters, CommandType.Text);
        }
        /// <summary>   
        /// 对数据库执行增删改操作，返回受影响的行数。   
        /// </summary>   
        /// <param name="sql">要执行的增删改的SQL语句</param>   
        /// <param name="parameters">执行增删改语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, IList<IDbDataParameter> parameters, CommandType commandType)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                command.Connection.Open();
                int affectedRows = command.ExecuteNonQuery();
                command.Connection.Close();
                return affectedRows;
            }
        }
        /// <summary>   
        /// 执行一个查询语句，返回一个关联的DataReader实例   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <returns></returns> 
        public IDataReader ExecuteReader(string sql, IList<IDbDataParameter> parameters)
        {
            return ExecuteReader(sql, parameters, CommandType.Text);
        }
        /// <summary>   
        /// 执行一个查询语句，返回一个关联的DataReader实例   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns> 
        public IDataReader ExecuteReader(string sql, IList<IDbDataParameter> parameters, CommandType commandType)
        {
            DbCommand command = CreateDbCommand(sql, parameters, commandType);
            command.Connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        /// <summary>   
        /// 执行一个查询语句，返回一个包含查询结果的DataTable   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, IList<IDbDataParameter> parameters)
        {
            return ExecuteDataTable(sql, parameters, CommandType.Text);
        }
        /// <summary>   
        /// 执行一个查询语句，返回一个包含查询结果的DataTable   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, IList<IDbDataParameter> parameters, CommandType commandType)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                using (DbDataAdapter adapter = ProviderFactory.CreateDataAdapter())
                {
                    adapter.SelectCommand = command;
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }
        /// <summary>   
        /// 执行一个查询语句，返回查询结果的第一行第一列   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>   
        /// <returns></returns>   
        public Object ExecuteScalar(string sql, IList<IDbDataParameter> parameters)
        {
            return ExecuteScalar(sql, parameters, CommandType.Text);
        }
        /// <summary>   
        /// 执行一个查询语句，返回查询结果的第一行第一列   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>   
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns>   
        public Object ExecuteScalar(string sql, IList<IDbDataParameter> parameters, CommandType commandType)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                command.Connection.Open();
                object result = command.ExecuteScalar();
                command.Connection.Close();
                return result;
            }
        }
        /// <summary>
        /// 创建一个DbCommand对象
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns>
        public DbCommand CreateDbCommand(string sql, IList<IDbDataParameter> parameters, CommandType commandType)
        {
            DbConnection connection = ProviderFactory.CreateConnection();
            DbCommand command = ProviderFactory.CreateCommand();
            connection.ConnectionString = ConnectionString;
            command.CommandText = sql;
            command.CommandType = commandType;
            command.Connection = connection;
            if (!(parameters == null || parameters.Count == 0))
            {
                foreach (DbParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }
        /// <summary>
        /// 分页获取对象列表
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="viewName">视图/表名称</param>
        /// <param name="conditions">SQL条件</param>
        /// <param name="sortExpression">排序</param>
        /// <param name="pageIndex">页序号</param>
        /// <param name="pageSize">每页大小</param>
        /// <param name="recordCount">数据总量</param>
        /// <param name="hanlder">从DataRader中获取实体的委托函数</param>
        /// <returns></returns>
        public List<T> GetDataListByCond<T>
            (string viewName, string conditions, string sortExpression,
            int pageIndex, int pageSize, out int recordCount, LoadFromReaderHanlder<T> hanlder)
        {
            if (string.IsNullOrEmpty(sortExpression) || string.IsNullOrEmpty(sortExpression.Trim()))
            {
                sortExpression = "1";
            }

            //Prepare the query conditions
            DBHelper.PrepareSqlConditions(ref conditions);

            recordCount = -1;
            //Get the recordCount only when pageIndex==0 
            if (pageIndex == 0)
            {
                //get the record count of this query
                string countSQL = "Select count(1) from {0} where 1=1 {1}";
                recordCount = System.Convert.ToInt32(this.ExecuteScalar(string.Format(countSQL, viewName, conditions), null));
            }

            //the fields of this qurey want to get
            string fields = "*";

            //get a page record 
            string queryString = "SELECT {0} FROM {1} "
                + " WHERE 1=1 {2}"
                + " ORDER BY {3} "
                + " LIMIT {4} OFFSET {5}";

            int offsetCount = pageSize * (pageIndex);
            if (offsetCount <= 0)
            {
                offsetCount = 0;
            }

            object[] paras = new object[] { fields, viewName, conditions, sortExpression, pageSize, offsetCount };

            List<T> list = new List<T>();

            T entity;
            using (System.Data.IDataReader reader = this.ExecuteReader(string.Format(queryString, paras), null))
            {
                while (reader.Read() == true)
                {
                    //构建对象
                    T localItem = default(T);
                    entity = (localItem == null) ? Activator.CreateInstance<T>() : default(T);
                    hanlder(reader, ref entity);
                    list.Add(entity);
                }
            }
            return list;
        }
        /// <summary>
        /// 分页获取数据表
        /// </summary>
        /// <param name="viewName">视图/表名称</param>
        /// <param name="conditions">SQL条件</param>
        /// <param name="sortExpression">排序</param>
        /// <param name="pageIndex">页序号</param>
        /// <param name="pageSize">每页大小</param>
        /// <param name="recordCount">数据总量(条)</param>
        /// <returns>数据表</returns>
        public DataTable GetDataListByCond
           (string viewName,string fields, string conditions, string sortExpression,
           int pageIndex, int pageSize, out int recordCount)
        {
            if (string.IsNullOrEmpty(sortExpression) || string.IsNullOrEmpty(sortExpression.Trim()))
            {
                sortExpression = "1";
            }
            //Prepare the query conditions
            DBHelper.PrepareSqlConditions(ref conditions);
            recordCount = -1;
            //Get the recordCount only when pageIndex==0 
            if (pageIndex == 0)
            {
                //get the record count of this query
                string countSQL = "Select count(1) from {0} where 1=1 {1}";
                recordCount = System.Convert.ToInt32(this.ExecuteScalar(string.Format(countSQL, viewName, conditions), null));
            }

            //the fields of this qurey want to get
            if (string.IsNullOrEmpty(fields))
            {
                fields = "*";
            }

            //get a page record 
            string queryString = "SELECT {0} FROM {1} "
                + " WHERE 1=1 {2}"
                + " ORDER BY {3} "
                + " LIMIT {4} OFFSET {5}";

            int offsetCount = pageSize * (pageIndex);
            if (offsetCount <= 0)
            {
                offsetCount = 0;
            }
            object[] paras = new object[] { fields, viewName, conditions, sortExpression, pageSize, offsetCount };
            DataTable dt = this.ExecuteDataTable(string.Format(queryString,paras),null);
            return dt;
        }

        /// <summary>
        /// 获取对象列表
        /// </summary>
        /// <param name="viewName"></param>
        /// <param name="fields"></param>
        /// <param name="conditions"></param>
        /// <param name="sortExpression"></param>
        /// <returns></returns>
        public DataTable GetDataTableByCond(string viewName, string fields, string conditions, string sortExpression)
        {
            int recordCount = 0;
            return this.GetDataListByCond(viewName,fields,conditions,sortExpression,0,int.MaxValue,out recordCount);
        }

        /// <summary>
        /// 获取数据库服务提供工厂实例
        /// </summary>
        /// <param name="providerInvariantName"></param>
        /// <returns></returns>
        public virtual DbProviderFactory GetDbProviderFactory()
        {
            return DbProviderFactories.GetFactory(this.ProviderInvariantName);
        }

        #region 公用辅助方法&函数
        public static string ConvertListToSQLString<T>(IList<T> list)
        {
            return string.Empty;
        }

        public static void PrepareSqlConditions(ref string conditions)
        {

        }
        public virtual IDbDataParameter GetDbDataParameter(string nativeType, string parameterName, object parameterValue)
        {
            return null;
        }
        #endregion

    }

    /// <summary>
    /// 数据库类型枚举
    /// </summary>
    public enum DbProviderType : byte
    {
        SqlServer,
        MySql,
        SQLite,
        Oracle,
        ODBC,
        OleDb,
        Firebird,
        PostgreSql,
        DB2,
        Informix,
        SqlServerCe
    }
}
